IF OBJECT_ID('dbo.RptCSCRApprovedAgreementSummary') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptCSCRApprovedAgreementSummary
    IF OBJECT_ID('dbo.RptCSCRApprovedAgreementSummary') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptCSCRApprovedAgreementSummary >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptCSCRApprovedAgreementSummary >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptCSCRApprovedAgreementSummary
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/1/2007
--
-- Description: Source of Report, "CSCRApprovedAgreementSummary"
--                  In Insight DB Reporting Services
--
-- Provides break-outs of CSCR work by agreement associate, transaction 
-- type, PHS entity and who initiated. This report provides useful data 
-- for CSCR management to better understand the characteristics of 
-- agreements completed.
--
-- Parameters:  
--             @StartDate          Created Date
--             @EndDate            
--             @DomainUserId
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptCSCRApprovedAgreementSummary()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/3/07     KC        Created
-- 10/18/07    KC        Fixed Searching on Dates with TimeComponent
-- 02/27/08    KC        Removed Security per Partners Request
-- 03/26/08    KC        Added 'OTHER' to result set
-- 04/08/08    KC        Added CSCRManagerId to ResultSet for Drilldown
--                       Found bug in Counts, Add CASE Statement on
--                       SubActivity to GROUP BY
-- 04/14/08    KC        Using new View for Amendments and Masters
-- 05/13/08    KC        Added @IncludeDanaFarber, Changed logic to count
--                       Deliverables
-- 05/15/08    KC        Remmoved DelStatus "_61" as it is Pending!
-- 05/21/08    KC        Fixed DanaFarber to use Function.
-- 05/22/08    KC        DFCPP changed to DFPCC
-- 06/09/08    KC        Finalized with feedback from Report Spec.
-- 06/17/08    KC        Bug with DFPCC, Bucket had to be changed as well
--                       from (DFCPP)
--
-- =======================================================================
CREATE PROCEDURE dbo.RptCSCRApprovedAgreementSummary
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @IncludeDanaFarber                 VARCHAR(3) = NULL,   
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT,
               @ConversionDate1 DATETIME,
               @ConversionDate2 DATETIME,
               @Institutions  VARCHAR(50)
              
     -- Default this, Records with these PropStatDate(s) will not be returned
     SET  @ConversionDate1 = '8/3/2007'
     SET  @ConversionDate2 = '8/4/2007'
          
     -- Check DanaFarber
     IF UPPER(@IncludeDanaFarber) != 'YES'
          SET @IncludeDanaFarber = NULL

     -- Set a list of Institutions
     IF @IncludeDanaFarber IS NOT NULL
          SET  @Institutions = 'BWH,MGH,NWH,SRH,DFCI,DFPCC'
     ELSE
          SET  @Institutions = 'BWH,MGH,NWH,SRH'
          
     -- Result Set
     SELECT    CSCRManagerId,
               CSCRCaseManagerName,
               ISNULL(MAX(BWHCTACount),0) AS BWHCTACount,
               ISNULL(MAX(BWHCDACount),0) AS BWHCDACount,
               ISNULL(MAX(BWHOTHERCount),0) AS BWHOTHERCount,
               
               ISNULL(MAX(MGHCTACount),0) AS MGHCTACount,
               ISNULL(MAX(MGHCDACount),0) AS MGHCDACount,
               ISNULL(MAX(MGHOTHERCount),0) AS MGHOTHERCount,
               
               ISNULL(MAX(NWHCTACount),0) AS NWHCTACount,
               ISNULL(MAX(NWHCDACount),0) AS NWHCDACount,
               ISNULL(MAX(NWHOTHERCount),0) AS NWHOTHERCount,
               
               ISNULL(MAX(SRHCTACount),0) AS SRHCTACount,
               ISNULL(MAX(SRHCDACount),0) AS SRHCDACount,
               ISNULL(MAX(SRHOTHERCount),0) AS SRHOTHERCount,
               
               ISNULL(MAX(DFCICTACount),0) AS DFCICTACount,
               ISNULL(MAX(DFCICDACount),0) AS DFCICDACount,
               ISNULL(MAX(DFCIOTHERCount),0) AS DFCIOTHERCount
               
     FROM
     (SELECT   CSCRManagerId,
               CSCRCaseManagerName,
               SUM(CASE WHEN Institution = 'BWH' AND SubActivityType = 'CTA'
                    THEN AgreementCount
               END) AS BWHCTACount,
               SUM(CASE WHEN Institution = 'BWH' AND SubActivityType = 'CDA'
                    THEN AgreementCount
               END) AS BWHCDACount,
               SUM(CASE WHEN Institution = 'BWH' AND SubActivityType = 'OTHER'
                    THEN AgreementCount
               END) AS BWHOTHERCount,
               
               SUM(CASE WHEN Institution = 'MGH' AND SubActivityType = 'CTA'
                    THEN AgreementCount
               END) AS MGHCTACount,
               SUM(CASE WHEN Institution = 'MGH' AND SubActivityType = 'CDA'
                    THEN AgreementCount
               END) AS MGHCDACount,
               SUM(CASE WHEN Institution = 'MGH' AND SubActivityType = 'OTHER'
                    THEN AgreementCount
               END) AS MGHOTHERCount,

               SUM(CASE WHEN Institution = 'NWH' AND SubActivityType = 'CTA'
                    THEN AgreementCount
               END) AS NWHCTACount,
               SUM(CASE WHEN Institution = 'NWH' AND SubActivityType = 'CDA'
                    THEN AgreementCount
               END) AS NWHCDACount,
               SUM(CASE WHEN Institution = 'NWH' AND SubActivityType = 'OTHER'
                    THEN AgreementCount
               END) AS NWHOTHERCount,
               
               SUM(CASE WHEN Institution = 'SRH' AND SubActivityType = 'CTA'
                    THEN AgreementCount
               END) AS SRHCTACount,
               SUM(CASE WHEN Institution = 'SRH' AND SubActivityType = 'CDA'
                    THEN AgreementCount
               END) AS SRHCDACount,
               SUM(CASE WHEN Institution = 'SRH' AND SubActivityType = 'OTHER'
                    THEN AgreementCount
               END) AS SRHOTHERCount,

               SUM(CASE WHEN Institution IN ('DFCI','DFPCC') AND SubActivityType = 'CTA'
                    THEN AgreementCount
               END) AS DFCICTACount,
               SUM(CASE WHEN Institution IN ('DFCI','DFPCC') AND SubActivityType = 'CDA'
                    THEN AgreementCount
               END) AS DFCICDACount,
               SUM(CASE WHEN Institution IN ('DFCI','DFPCC') AND SubActivityType = 'OTHER'
                    THEN AgreementCount
               END) AS DFCIOTHERCount               
               
     FROM
          (SELECT   ISNULL(AI.CSCRManagerId,-1) AS CSCRManagerId,
                    ISNULL(AI.CSCRCaseManagerName,'Unassigned') AS CSCRCaseManagerName,
                    AI.Institution,
                    CASE WHEN AI.SubActivityType LIKE 'Trial%' THEN 'CTA'
                         WHEN AI.SubActivityType LIKE 'CDA%' THEN 'CDA'
                         ELSE 'OTHER'
                    END AS SubActivityType,
                    COUNT(AI.FolderNumber) AS AgreementCount
          FROM      dbo.AgrAgreement_Info AI INNER JOIN

                    -- New logic. Count Deliverables, they they assume Activity codes of Parent Agreement
                    (SELECT  D.prop_no,
                            D.ReportId,
                            D.ReportName,
                            D.REPORTCAT,
                            MIN(D.ProcessedDate) AS CSCRCompletedDate
                    FROM    dbo.vwInfoEdCSCRDeliverables D
                    WHERE   D.REPORTCAT_CODE IN ('REPORTCAT_19',    --     CSCR - Contract
                                                 'REPORTCAT_32',    --     CSCR - Agreement
                                                 'REPORTCAT_33',    --     CSCR - Amendment
                                                 'REPORTCAT_34',    --     CSCR - Subcontract
                                                 'REPORTCAT_36')    --     CSCR - Sub-Amendment
                            AND D.REPORTSTAT_CODE = 'REPORTSTAT_13' --     CSCR - Completed
                    GROUP BY D.prop_no,
                            D.ReportId,
                            D.ReportName,
                            D.REPORTCAT ) DELS ON AI.InfoEdPropNumber = DELS.prop_no

          
          WHERE     (dbo.fnRemoveTime(DELS.CSCRCompletedDate) BETWEEN @StartDate AND @EndDate ) AND    
                    (RTRIM(AI.ActivityDescription) = 'Clinical Research') AND
                    (AI.Institution IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Institutions))) 
                    
          GROUP BY  ISNULL(AI.CSCRManagerId,-1), 
                    ISNULL(AI.CSCRCaseManagerName,'Unassigned'), 
                    AI.Institution,
                    CASE WHEN AI.SubActivityType LIKE 'Trial%' THEN 'CTA'
                         WHEN AI.SubActivityType LIKE 'CDA%' THEN 'CDA'
                         ELSE 'OTHER'
                    END) COUNTS
          GROUP BY  COUNTS.CSCRManagerId,
                    COUNTS.CSCRCaseManagerName
     ) GRPED
     GROUP BY  CSCRManagerId,
               CSCRCaseManagerName
     ORDER BY  CSCRCaseManagerName
     


     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1
          
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptCSCRApprovedAgreementSummary') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptCSCRApprovedAgreementSummary >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptCSCRApprovedAgreementSummary >>>'
go

GRANT EXECUTE ON dbo.RptCSCRApprovedAgreementSummary TO InsightReport,Insight
go

